/*
 * Copyright (C) Francisco Morero Peyrona. All rights reserved.
 *
 * This software is published under the terms of Open Source
 * License version 1.1, a copy of which has been included with 
 * this distribution in the License.txt file.
 */

package agents;

import java.sql.*;
import java.util.*;

import javax.swing.tree.*;

import jdbcmanager.diagram.*;
import jdbcmanager.app.*;
import jdbcmanager.dbtools.DbUtil;
import jdbcmanager.dbtools.agentwise.*;
import jdbcmanager.dbtools.datatypes.*;

/**
 * See interface </code>AgentMetaData</code>: this class is its default implementation.
 *
 * @author Francisco Morero Peyrona
 */
public class AgentMetaDataDefault extends DbMetadataWrapper implements AgentMetaData
{
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public void setMetaData( DatabaseMetaData dbmd )
   {
      super.setMetaData( dbmd );
   }

   //-------------------------------------------------------------------------
   // Tables related methods
   //-------------------------------------------------------------------------
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public boolean existsTable( DbEntityDescriptor table )
   {
      String[]             asType = (table.getType() == null) ? null : new String[] {table.getType()};
      DbEntityDescriptor[] aTable = getTables( table.getCatalog(), table.getSchema(), table.getName(), asType );

      return aTable.length > 0;
   }
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public DbEntityDescriptor[] removeHiddenTables( DbEntityDescriptor[] aTable )
   {
      DbEntityDescriptor[] aRet;
      int n = 0;
      
      for( ; n < aTable.length; n++ )   // Search if diagram table is in the catalog
      {
         if( aTable[n].getName().equals( App.sDIAGRAM_TABLE ) )
            break; // Because there can be only one Diagram table per Catalog
      }

      if( n < aTable.length )   // There is a table named: <Constant.sDIAGRAM_TABLE>
      {
         aRet = new DbEntityDescriptor[ aTable.length - 1 ];

         System.arraycopy( aTable, 0, aRet, 0, n );       // From 0 to the previous element to Diagram table

         if( n+1 < aTable.length )   // Was not the last one
            System.arraycopy( aTable, n+1, aRet, n, aTable.length-n-1 ); // From n+1 to the end of the original array
      }
      else
      {
         aRet = aTable;
      }
      
      return aRet;
   }
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public DefaultMutableTreeNode[] removeHiddenTables( DefaultMutableTreeNode[] aTable )
   {
      boolean bContinue = true;
      
      for( int n = 0; n < aTable.length && bContinue; n++ )
      {            
         for( Enumeration e = aTable[n].preorderEnumeration(); e.hasMoreElements() && bContinue; )
         {
            DefaultMutableTreeNode node = (DefaultMutableTreeNode) e.nextElement();
            DbEntityDescriptor     ded  = (DbEntityDescriptor) node.getUserObject();
             
            if( ded.getName().equals( App.sDIAGRAM_TABLE ) )
            {
               node.removeFromParent();
               bContinue = false;   // Because there can be only one Diagrams table per Catalog
            }
         }
      }

      return aTable;
   }
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public DefaultMutableTreeNode[] getTableTree( String sCatalog, String sSchema )
   {
      String[] asType = getTableTypes();
         
      if( asType != null && asType.length > 0 )
          return getTableTree( sCatalog, sSchema, asType );
      
      return getTableTree( sCatalog, sSchema, (String) null );
   }
   
   /**
    * Return an array of <code>DefaultMutableTreeNode</code> were each element of the array
    * is a TableType and their childs are the nodes (table names) of the type.
    *
    * @param sCatalog     A catalog               (or null)
    * @param sSchema      A schema                (or null)
    * @param sTableTypes  An array of table types (can't be null)
    * @return An array of <code>DefaultMutableTreeNode</code>
    * @throws SQLException Thrown if an SQL error occurs.
    */
   protected DefaultMutableTreeNode[] getTableTree( String sCatalog, String sSchema, String[] asTableType )
   {
      Vector vNodes = new Vector();

      for( int n = 0; n < asTableType.length; n++ )
      {
         DefaultMutableTreeNode   nodeType = new DefaultMutableTreeNode( new DbEntityDescriptor( sCatalog, sSchema, asTableType[n] ) );
                                  nodeType.setAllowsChildren( true );
         DefaultMutableTreeNode[] aNodeTbl = getTableTree( sCatalog, sSchema, asTableType[n] );

         for( int x = 0; x < aNodeTbl.length; x++ )
            nodeType.add( aNodeTbl[x] );

         vNodes.add( nodeType );
      }

      DefaultMutableTreeNode[] aoRet = new DefaultMutableTreeNode[ vNodes.size() ];
      vNodes.copyInto( aoRet );

      return aoRet;
   }

   /**
    * Return an array of <code>DefaultMutableTreeNode</code> were each element of the array
    * is a node representing the Table name.
    *
    * @param sCatalog        A catalog        (or null)
    * @param sSchemaPattern  A schema pattern (or null)
    * @param sTableType      A table type     (or null)
    * @return An array of <code>DefaultMutableTreeNode</code>
    * @throws SQLException Thrown if an SQL error occurs.
    */
   protected DefaultMutableTreeNode[] getTableTree( String sCatalog, String sSchemaPattern, String sTableType )
   {
      String[]                 asType = (sTableType == null) ? null : new String[] {sTableType};
      DbEntityDescriptor[]     aTable = getTables( sCatalog, sSchemaPattern, null, asType );
      DefaultMutableTreeNode[] aRet   = new DefaultMutableTreeNode[ aTable.length ];
      
      for( int n = 0; n < aRet.length; n++ )
         aRet[n] = new DefaultMutableTreeNode( aTable[n], false );
      
      return aRet;
   }

   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public TableColumnDefinition[] getTableColumns( DbEntityDescriptor table )
   {
      Vector vCol = new Vector();
      
      try
      {
         // Find Primary Keys ----------------------------------------------------//
         Vector    vKey  = new Vector();      
         ResultSet rsKey = getPrimaryKeys( table.getCatalog(), table.getSchema(), table.getName() );
   
         while( rsKey.next() )
            vKey.add( rsKey.getString( 4 ) );
   
         rsKey.close();
   
         // From Vector to array
         String[] asKey = new String[ vKey.size() ];
         vKey.copyInto( asKey );
         //-----------------------------------------------------------------------//
   
         // DatabaseMetaData.getColumns() does not provides information about if a column 
         // is auto-increment or not, but it provides information about the data type 
         // (java.sql.Types, rs.getInt(5)).
         // Therefore, by using DatabaseMetaData.getTypeInfo() we can infer this value.
         TableColumnTypeDefinition[] aoTCTD = DbUtil.getAgentDBMS().getAgentMetaData().getSupportedColumnTypes();
         
         ResultSet rs = getColumns( table.getCatalog(), table.getSchema(), table.getName() );
   
         while( rs.next() )
         {
            String  sName     = rs.getString(	"COLUMN_NAME" );
            int     nDataType = rs.getInt(		"DATA_TYPE" );        // SQL type from java.sql.Types
            int     nSize     = rs.getInt(		"COLUMN_SIZE" );
            int     nScale    = rs.getInt(		"DECIMAL_DIGITS" );  // TODO: DECIMAL_DIGITS is nScale ???
            boolean bNullable = (rs.getInt(		"NULLABLE" ) == DatabaseMetaData.columnNullable);
            String  sDefault  = rs.getString(	"COLUMN_DEF" );
			int     nPtr      = TableColumnTypeDefinition.searchOnType( aoTCTD, nDataType );
            boolean bIsKey    = App.indexOf( asKey, sName, false ) > -1;
            boolean bAutoInc  = aoTCTD[ nPtr ].isAutoIncrementable();
            
            vCol.add( new TableColumnDefinition( sName, aoTCTD[ nPtr ], nSize, nScale, bNullable, bAutoInc, bIsKey, sDefault ) );
         }
         
         rs.close();
      }
      catch( SQLException exc )
      {
         App.showException( exc, "Error accessing table columns definition" );
      }
      
      // From Vector to array
      TableColumnDefinition[] aRet = new TableColumnDefinition[ vCol.size() ];
      vCol.copyInto( aRet );

      return aRet;
   }

   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public IndexDefinition[] getTableIndices( DbEntityDescriptor table  )
   {
      Hashtable         htNdx = new Hashtable();   // Key = sTableName + sIndexName
      ResultSet         rs    = getIndexInfo( table.getCatalog(), table.getSchema(), table.getName(), false, true );
      IndexDefinition[] aRet  = new IndexDefinition[0];
      
      if( rs != null )
      {
         try
         {
            while( rs.next() )
            {
               if( rs.getShort( 7 ) != DatabaseMetaData.tableIndexStatistic )
               {
                  String          sTable = rs.getString( 3 );   // Table name
                  String          sIndex = rs.getString( 6 );   // Index name
                  IndexDefinition index  = (IndexDefinition) htNdx.get( sTable + sIndex );
      
                  if( index == null )
                  {
                     index = new IndexDefinition( new DbEntityDescriptor( table.getCatalog(), table.getSchema(), sIndex ), sTable );
                     htNdx.put( sTable + sIndex, index );
                  }
      
                  String  sAscDesc   = rs.getString( 10 );   // "A"sc or "D"esc
                  boolean bAscending = ! (sAscDesc == null || sAscDesc.trim().charAt( 0 ) == 'D');
      
                                // ColumnName           NonUnique           Ascending   Filter condition
                  index.addColumn( rs.getString( 9 ), ! rs.getBoolean( 4 ), bAscending, rs.getString( 13 ) );
               }
            }
         }
         catch( SQLException exc )
         {
            App.showException( exc );   
         }
         finally
         {
            try{ rs.close(); } catch( SQLException exc ) { /* Nothing to do */ }
         }
         
         // From Vector to array
         aRet = new IndexDefinition[ htNdx.size() ];
         int n = 0;
   
         for( Enumeration e = htNdx.elements(); e.hasMoreElements(); )
            aRet[n++] = (IndexDefinition) e.nextElement();
      }
      
      return aRet;
   }
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public RelationDefinition[] getTableImportedRelations( DbEntityDescriptor table )
   {
   	  RelationDefinition[] aRet       = new RelationDefinition[0];
      ResultSet            rsImported = getImportedKeys( table.getCatalog(), table.getSchema(), table.getName() );
      
      if( rsImported != null )
      	aRet = processRelationResulSet( rsImported );
      
      return aRet;
   }
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public RelationDefinition[] getTableExportedRelations( DbEntityDescriptor table )
   {
   	  RelationDefinition[] aRet       = new RelationDefinition[0];
      ResultSet            rsExported = getExportedKeys( table.getCatalog(), table.getSchema(), table.getName() );
      
      if( rsExported != null )
        aRet = processRelationResulSet( rsExported );
      
      return aRet;
   }
   
   private RelationDefinition[] processRelationResulSet( ResultSet rs )
   {
   	Vector vRelation = new Vector();
    
      try
      {
         while( rs.next() )
         {
            String sPrimaryTable   = rs.getString(  3 );    // PKTABLE_NAME
            String sPrimaryColumn  = rs.getString(  4 );    // PKCOLUMN_NAME
            String sForeignTable   = rs.getString(  7 );    // FKTABLE_NAME
            String sForeignColumn  = rs.getString(  8 );    // FKCOLUMN_NAME
            short  nKeySequence    = rs.getShort(   9 );    // KEY_SEQ
            short  nUpdateRule     = rs.getShort(  10 );    // UPDATE_RULE
            short  nDeleteRule     = rs.getShort(  11 );    // DELETE_RULE
            String sForeignKeyName = rs.getString( 12 );    // FK_NAME
            String sPrimaryKeyName = rs.getString( 13 );    // PK_NAME
            
            if( nKeySequence == 1 )   // A new relation starts (otherwise it is part of the previous relation)
            {
            	 RelationDefinition rd = new RelationDefinition();
            	                    rd.setPrimaryTable( sPrimaryTable );
                                    rd.setForeignTable( sForeignTable );
                                    rd.setUpdateRule( nUpdateRule );
                                    rd.setDeleteRule( nDeleteRule );
               vRelation.add( rd );
            }
            
            ((RelationDefinition) vRelation.lastElement()).
				addColumn( new RelationColumnDefinition( sPrimaryColumn, sForeignColumn, sPrimaryKeyName, sForeignKeyName ) );
         }
        
         rs.close();
      }
      catch( SQLException exc )
      {
         vRelation.removeAllElements();
      }
      
      // From Vector to array
      RelationDefinition[] aRet = new RelationDefinition[ vRelation.size() ];
      vRelation.copyInto( aRet );
      
      return aRet;
   }
   
   // -------------------------------------------------------------------------
   // Diagrams related methods
   //------------------------------------------------------------------------- 

   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
    public DefaultMutableTreeNode[] getDiagramTree( String sCatalog, String sSchema )
    {
       String[]                 asName = DbDiagram.getDiagramNames( sCatalog, sSchema );
       DefaultMutableTreeNode[] aRet   = new DefaultMutableTreeNode[ asName.length ];
       
       for( int n = 0; n < aRet.length; n++ )
       {
          DbEntityDescriptor ded = new DbEntityDescriptor( sCatalog, sSchema, asName[n], "DIAGRAM" );
          aRet[n] = new DefaultMutableTreeNode( ded, false );  // false because doesn't allow children --> It's an Diagram node
       }
       
       return aRet;
    }
    
    public int getMaxDiagramNameLength()
    {
       return 64;   // The max length for a varchar colum is 255, but 64 chars are more than enough for a name
    }
 
   //-------------------------------------------------------------------------
   // Stored Procedures related methods
   //-------------------------------------------------------------------------
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public DefaultMutableTreeNode[] getProcedureTree( String sCatalog, String sSchema )
   {
      DbEntityDescriptor[]     aDed = getProcedures( sCatalog, sSchema, null ); 
      DefaultMutableTreeNode[] aRet = new DefaultMutableTreeNode[ aDed.length ];
      
      for( int n = 0; n < aDed.length; n++ )
         aRet[n] = new DefaultMutableTreeNode( aDed[n], false );  // false 'cause doesn't allow children --> It's an Stored Procedure node
      
      return aRet;
   }
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
 * @throws SQLException
    */
   public StoredProcedureDefinition getProcedure( DbEntityDescriptor ded )
   {
      StoredProcedureDefinition sp   = null;
      DbEntityDescriptor[]      aDed = getProcedures( ded.getCatalog(), ded.getSchema(), ded.getName() );

      if( aDed.length > 0 )
      {
         sp = new StoredProcedureDefinition( aDed[0] );
         
         try
         {
            addColumns2StoredProcedure( sp );
         }
         catch( SQLException exc )
         {
            App.showException( exc );
         }
      }

      return sp;
   }

   //-------------------------------------------------------------------------
   // User Defined Type related methods
   //-------------------------------------------------------------------------
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public DefaultMutableTreeNode[] getUDTTree( String sCatalog, String sSchema )
   {
      DbEntityDescriptor[]     aDed = getUDTs( sCatalog, sSchema, null ); 
      DefaultMutableTreeNode[] aRet = new DefaultMutableTreeNode[ aDed.length ];
      
      for( int n = 0; n < aDed.length; n++ )
         aRet[n] = new DefaultMutableTreeNode( aDed[n], false );  // false 'cause doesn't allow children --> It's an Stored Procedure node
      
      return aRet;
   }   
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public UDTDefinition getUDT( DbEntityDescriptor ded )
   {
   	    UDTDefinition udt = null;
        ResultSet rs = getUDTColumns( ded.getCatalog(), ded.getSchema(), ded.getName(), null );

        try
        {
            if( rs.next() )
            {
                udt = new UDTDefinition( ded );
                udt.setClassName( rs.getString( 4 ) );
                udt.setDataType( rs.getInt( 5 ) );
                udt.setComments( rs.getString( 6 ) );
            }
            
            rs.close();
        }
        catch( SQLException exc )
        {
            App.showDebugException( exc );
        }

        return udt;
   }

   //-------------------------------------------------------------------------
   // All other methods
   //-------------------------------------------------------------------------
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */
   public String[] getExtraSQLKeywords()
   {
      return StrWise.string2Array( getSQLKeywords(), "," );
   }
   
   /**
    * See <code>AgentMetaData</code> interface for documentation
    */   
   public Hashtable getPrebuiltFunctions()
   {
      Hashtable ht = new Hashtable( 4 );

      ht.put( "Numeric"    , StrWise.string2Array( getNumericFunctions() , "," ) );
      ht.put( "String"     , StrWise.string2Array( getStringFunctions()  , "," ) );
      ht.put( "Date & Time", StrWise.string2Array( getTimeDateFunctions(), "," ) );
      ht.put( "System"     , StrWise.string2Array( getSystemFunctions()  , "," ) );

      return ht;
   }
   
   //-------------------------------------------------------------------------//
   // PROTECTED METHODS
   // These obviously are not defined in the interface that is implemented by
   // this class, but are used by public methods in this class.
   //-------------------------------------------------------------------------//

   /**
    * Retrieves whether this database supports catalogs at all.
    *
    * @return	<code>true</code> if database supports catalogs.
    * @throws	SQLException	Thrown if an SQL error occurs
    */
   protected boolean supportsCatalogs()
   {
      return supportsCatalogsInTableDefinitions() ||
             supportsCatalogsInDataManipulation() ||
             supportsCatalogsInProcedureCalls();
   }

   //-------------------------------------------------------------------------//
   // PRIVATE METHODS
   // These obviously are not defined in the interface that is implemented by
   // this class, but are used by public methods in this class.
   //-------------------------------------------------------------------------//
   
   // Fills an StoreProcedure structure with its proper information
   private void addColumns2StoredProcedure( StoredProcedureDefinition sp ) throws SQLException
   {
      ResultSet rs = getProcedureColumns( sp.getCatalog(), sp.getSchema(), sp.getName() );

      while( rs.next() )
      {
         String sName           = rs.getString(  4 );
         short  nReturn         = rs.getShort(   5 );
         int    nSqlDataType    = rs.getShort(   6 );
         String sReturnTypeName = rs.getString(  7 );
         int    nPrecision      = rs.getInt(     8 );
         int    nByteLength     = rs.getInt(     9 );
         short  nScale          = rs.getShort(  10 );
         short  nRadix          = rs.getShort(  11 );
         short  nNullable       = rs.getShort(  12 );
         String sRemarks        = rs.getString( 13 );

         // Interpret the return type (readable for humans)
         String sReturn = "Unknown";

         switch( nReturn )
         {
            case DatabaseMetaData.procedureColumnIn    : sReturn = "In";               break;
            case DatabaseMetaData.procedureColumnOut   : sReturn = "Out";              break;
            case DatabaseMetaData.procedureColumnInOut : sReturn = "In/Out";           break;
            case DatabaseMetaData.procedureColumnReturn: sReturn = "return value";     break;
            case DatabaseMetaData.procedureColumnResult: sReturn = "return ResultSet";
         }

         // Interpret SqlDataType
         String sSqlDataType = JDBCTypeConverter.getJdbcTypeName( nSqlDataType );

         // Interpret nullable
         String sNullable = "";

         switch( nNullable )
         {
            case DatabaseMetaData.procedureNoNulls         : sNullable = "False";  break;
            case DatabaseMetaData.procedureNullable        : sNullable = "True";   break;
            case DatabaseMetaData.procedureNullableUnknown : sNullable = "Unknown";
         }

         // Add to passed StoredProcedure
         sp.addColumn( sName, sReturn, sSqlDataType, sReturnTypeName, nPrecision,
                       nByteLength, nScale, nRadix, sNullable, sRemarks );
      }
      
      rs.close();
   }
}